{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-13T13:28:05.898836Z",
     "iopub.status.busy": "2023-11-13T13:28:05.898665Z",
     "iopub.status.idle": "2023-11-13T13:28:06.686806Z",
     "shell.execute_reply": "2023-11-13T13:28:06.686150Z",
     "shell.execute_reply.started": "2023-11-13T13:28:05.898813Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "\n",
    "stage = 'B'\n",
    "root = '../../../contest'\n",
    "\n",
    "df_train = pd.read_csv(os.path.join(root, 'train/GSLD_AGET_PAY.csv'))\n",
    "df_test = pd.read_csv(os.path.join(root, '{}/GSLD_AGET_PAY_{}.csv'.format(stage, stage)))\n",
    "\n",
    "save_path = '../data'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-13T13:28:06.688162Z",
     "iopub.status.busy": "2023-11-13T13:28:06.687948Z",
     "iopub.status.idle": "2023-11-13T13:28:06.693233Z",
     "shell.execute_reply": "2023-11-13T13:28:06.692585Z",
     "shell.execute_reply.started": "2023-11-13T13:28:06.688136Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "def perpro(df_train):\n",
    "    df_train['DATE'] = pd.to_datetime(df_train['DATE'], format='%Y%m%d')\n",
    "    df_train = df_train.sort_values('DATE')\n",
    "\n",
    "    df_train['aget_day_diff'] = df_train.groupby('CUST_NO')['DATE'].diff().dt.days\n",
    "    df_train = df_train.dropna()\n",
    "\n",
    "    tmp_df = df_train.groupby('CUST_NO').agg(\n",
    "        aget_day_diff_max = ('aget_day_diff', 'max'),\n",
    "        aget_day_diff_min = ('aget_day_diff', 'min')\n",
    "    ).reset_index()\n",
    "    return tmp_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-13T13:28:06.694713Z",
     "iopub.status.busy": "2023-11-13T13:28:06.694475Z",
     "iopub.status.idle": "2023-11-13T13:28:18.414685Z",
     "shell.execute_reply": "2023-11-13T13:28:18.414054Z",
     "shell.execute_reply.started": "2023-11-13T13:28:06.694676Z"
    }
   },
   "outputs": [],
   "source": [
    "df_train = perpro(df_train)\n",
    "df_test = perpro(df_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-13T13:28:18.416081Z",
     "iopub.status.busy": "2023-11-13T13:28:18.415869Z",
     "iopub.status.idle": "2023-11-13T13:28:18.619251Z",
     "shell.execute_reply": "2023-11-13T13:28:18.618637Z",
     "shell.execute_reply.started": "2023-11-13T13:28:18.416057Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# 代发工资间隔最大最小天数\n",
    "df_train.to_csv(save_path+'/add_aget_diff', index=False)\n",
    "df_test.to_csv(save_path+'/add_aget_diff_{}'.format(stage), index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-13T13:28:18.620415Z",
     "iopub.status.busy": "2023-11-13T13:28:18.620220Z",
     "iopub.status.idle": "2023-11-13T13:28:31.915530Z",
     "shell.execute_reply": "2023-11-13T13:28:31.914870Z",
     "shell.execute_reply.started": "2023-11-13T13:28:18.620392Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_train = pd.read_csv(os.path.join(root, 'train/GSLD_AGET_PAY.csv'))\n",
    "df_test = pd.read_csv(os.path.join(root, '{}/GSLD_AGET_PAY_{}.csv'.format(stage, stage)))\n",
    "\n",
    "df_train_aps = pd.read_csv(os.path.join(root, 'train/GSLD_TR_APS.csv'))\n",
    "df_test_aps = pd.read_csv(os.path.join(root, '{}/GSLD_TR_APS_{}.csv'.format(stage, stage)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-13T13:28:31.916750Z",
     "iopub.status.busy": "2023-11-13T13:28:31.916551Z",
     "iopub.status.idle": "2023-11-13T13:28:31.923573Z",
     "shell.execute_reply": "2023-11-13T13:28:31.922892Z",
     "shell.execute_reply.started": "2023-11-13T13:28:31.916726Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "def perpro2(df_train, df_train_aps):\n",
    "    df_train_aps = df_train_aps[['DATE', 'CUST_NO', 'APSDTRAMT']]\n",
    "    df_combine = df_train[['DATE', 'CUST_NO', 'TR_AMT']].merge(df_train_aps, how='left', on=['CUST_NO', 'DATE'])\n",
    "\n",
    "    tmp_df = df_combine.groupby(['CUST_NO','DATE'])\\\n",
    "                .agg({'APSDTRAMT':'sum'}).reset_index()\n",
    "\n",
    "    tmp_df1 = tmp_df.groupby('CUST_NO')\\\n",
    "                .agg(aps_aget_cnt = ('APSDTRAMT', 'count'))\n",
    "    \n",
    "    tmp_df2 = tmp_df[tmp_df.APSDTRAMT<=0]\\\n",
    "                .groupby('CUST_NO')\\\n",
    "                    .agg(\n",
    "                aps_aget_minus_cnt = ('APSDTRAMT', 'count')\n",
    "            )\n",
    "\n",
    "    tmp_df = tmp_df1.merge(tmp_df2, \\\n",
    "                           how='left', on='CUST_NO')\\\n",
    "                                .fillna(0).reset_index()\n",
    "    \n",
    "    tmp_df['aps_aget_day_minus_ratio'] = \\\n",
    "        tmp_df['aps_aget_minus_cnt'] / tmp_df['aps_aget_cnt']\n",
    "    \n",
    "    del tmp_df['aps_aget_minus_cnt']\n",
    "    return tmp_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-13T13:28:31.924605Z",
     "iopub.status.busy": "2023-11-13T13:28:31.924428Z",
     "iopub.status.idle": "2023-11-13T13:28:35.523286Z",
     "shell.execute_reply": "2023-11-13T13:28:35.522625Z",
     "shell.execute_reply.started": "2023-11-13T13:28:31.924582Z"
    }
   },
   "outputs": [],
   "source": [
    "df_train = perpro2(df_train, df_train_aps)\n",
    "df_test = perpro2(df_test, df_test_aps)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-13T13:28:35.525014Z",
     "iopub.status.busy": "2023-11-13T13:28:35.524795Z",
     "iopub.status.idle": "2023-11-13T13:28:35.701868Z",
     "shell.execute_reply": "2023-11-13T13:28:35.701281Z",
     "shell.execute_reply.started": "2023-11-13T13:28:35.524989Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# 代发工资当天流失\n",
    "df_train.to_csv(save_path+'/add_aps_aget', index=False)\n",
    "df_test.to_csv(save_path+'/add_aps_aget_{}'.format(stage), index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-13T13:28:35.703065Z",
     "iopub.status.busy": "2023-11-13T13:28:35.702864Z",
     "iopub.status.idle": "2023-11-13T13:28:35.720094Z",
     "shell.execute_reply": "2023-11-13T13:28:35.719562Z",
     "shell.execute_reply.started": "2023-11-13T13:28:35.703040Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CUST_NO</th>\n",
       "      <th>aps_aget_cnt</th>\n",
       "      <th>aps_aget_day_minus_ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000058da892ce16e1a133554d0e3be49</td>\n",
       "      <td>3</td>\n",
       "      <td>0.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0005b63f4a2eb0b50bcf381499d2212a</td>\n",
       "      <td>4</td>\n",
       "      <td>0.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000695df9c5398364428c23b57373a66</td>\n",
       "      <td>3</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000757b6d7c883d2409a48fd70a75908</td>\n",
       "      <td>3</td>\n",
       "      <td>0.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000b1d404edf83951b439684380689ae</td>\n",
       "      <td>4</td>\n",
       "      <td>0.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48115</th>\n",
       "      <td>fffc731675a8edbf059f2ee6e0280049</td>\n",
       "      <td>4</td>\n",
       "      <td>0.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48116</th>\n",
       "      <td>fffc895a4b526341ae514562c0ef64fa</td>\n",
       "      <td>2</td>\n",
       "      <td>0.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48117</th>\n",
       "      <td>fffd9a3c92135ec3f86dbae3cc0af63f</td>\n",
       "      <td>3</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48118</th>\n",
       "      <td>fffe18ebc9974334c1c06bf6e65451bd</td>\n",
       "      <td>2</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48119</th>\n",
       "      <td>ffffa4f720f6bc2aea7112de04619923</td>\n",
       "      <td>4</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>48120 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                CUST_NO  aps_aget_cnt  \\\n",
       "0      000058da892ce16e1a133554d0e3be49             3   \n",
       "1      0005b63f4a2eb0b50bcf381499d2212a             4   \n",
       "2      000695df9c5398364428c23b57373a66             3   \n",
       "3      000757b6d7c883d2409a48fd70a75908             3   \n",
       "4      000b1d404edf83951b439684380689ae             4   \n",
       "...                                 ...           ...   \n",
       "48115  fffc731675a8edbf059f2ee6e0280049             4   \n",
       "48116  fffc895a4b526341ae514562c0ef64fa             2   \n",
       "48117  fffd9a3c92135ec3f86dbae3cc0af63f             3   \n",
       "48118  fffe18ebc9974334c1c06bf6e65451bd             2   \n",
       "48119  ffffa4f720f6bc2aea7112de04619923             4   \n",
       "\n",
       "       aps_aget_day_minus_ratio  \n",
       "0                      0.333333  \n",
       "1                      0.250000  \n",
       "2                      1.000000  \n",
       "3                      0.666667  \n",
       "4                      0.750000  \n",
       "...                         ...  \n",
       "48115                  0.750000  \n",
       "48116                  0.500000  \n",
       "48117                  0.000000  \n",
       "48118                  1.000000  \n",
       "48119                  1.000000  \n",
       "\n",
       "[48120 rows x 3 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
